# pip install peewee 映射数据库 方便使用数据库，不用去手写SQL代码
# 官方文档 http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#quickstart
from peewee import *
from datetime import date

db = MySQLDatabase("spider_db"
                   , host="localhost"
                   , port=3306
                   , user="root"
                   , password="123456")


class Person(Model):
    # http://docs.peewee-orm.com/en/latest/peewee/models.html#field-types-table
    # 具体的字段类型与MySQL字段类型的映射关系，可以查看上述链接描述
    # name = CharField()
    name = CharField(max_length=20)
    birthday = DateField(null=True)
    # 不指定主键默认会生成以id为主键的字段，如果指定了主键，就不会自动生成了
    number = IntegerField(primary_key=True)

    # 这张表对应的数据库
    class Meta:
        database = db  # This model uses the "people.db" database.
        table_name = "person"  # 指定表名


# 通过peewee实现对mysql的增删改查

# 插入一条数据
def insert_person(name, birthday):
    insert_person_bean = Person(name=name, birthday=birthday)
    insert_person_bean.save()


# 根据名字查询一条数据
def find_person_by_name(name):
    # 查询单条数据，没有查询到的话会抛出异常，一定要做异常处理
    try:
        find_person_bean = Person.select().where(Person.name == name).get()
        return find_person_bean
    except Exception as e:
        print("find_person_by_name data not found. error = " + str(e))
        return


# 根据名字查询所有为改名字的数据
def findall_person_by_name(name):
    return Person.select().where(Person.name == name)


# 根据名字更新数据
def update_person_by_name(name, birthday):
    update_person_bean = find_person_by_name(name)
    if update_person_bean:
        update_person_bean.birthday = birthday
        update_person_bean.save()
        print("update person successful. name = " + name)
    else:
        print("update person failed. not find person")


# 根据名字删除数据
def delete_person_by_name(name):
    delete_person_bean = find_person_by_name(name)
    if delete_person_bean:
        delete_person_bean.delete_instance()
        print("delete person successful. name = " + name)
    else:
        print("delete person failed. not find person")


if __name__ == '__main__':
    # 创建表
    db.create_tables([Person])
    insert_person("Toosan2", date(1996, 12, 15))
    person1 = find_person_by_name("Toosan2")
    if person1:
        print(person1.name)
        print(person1.birthday)
    update_person_by_name("Toosan", date(2000, 12, 15))
    insert_person("Toosan1", date(1996, 12, 15))
    delete_person_by_name("Toosan1")
    persons = findall_person_by_name("Toosan")
    for person in persons:
        person.birthday = date(2002, 12, 15)
        person.save()
